﻿using FRMS.Helper;
using FRMS.Model;
using System;
using System.Collections.Generic;

namespace FRMS.DAL
{
    public class AttendanceDAL
    {
        public List<AttendanceInfo> QueryCheckInOut(CheckInOutInfo checkInOutInfo)
        {
            string strSql = "SELECT USERINFO.USERID,CHECKINOUT.CHECKTIME,USERINFO.NAME,USERINFO.BADGENUMBER,CHECKINOUT.Memoinfo,CHECKINOUT.RecordID FROM CHECKINOUT left JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID";
            string sqlwhere = string.Empty;
            if (!string.IsNullOrEmpty(checkInOutInfo.devideid))
            {
                sqlwhere += " CHECKINOUT.Memoinfo='" + checkInOutInfo.devideid + "' AND";
            }
            if (!string.IsNullOrEmpty(checkInOutInfo.name))
            {
                sqlwhere += " USERINFO.NAME like'%" + checkInOutInfo.name + "%' AND";
            }
            if (checkInOutInfo.DEPTID > 0)
            {
                sqlwhere += " USERINFO.DEFAULTDEPTID=" + checkInOutInfo.DEPTID + " AND";
            }
            if (!string.IsNullOrEmpty(checkInOutInfo.starttime))
            {
                if (Convert.ToDateTime(checkInOutInfo.starttime).ToString("HH:mm:ss") != "00:00:00")
                {
                    sqlwhere += " CHECKINOUT.CHECKTIME>='" + Convert.ToDateTime(checkInOutInfo.starttime).ToString("yyyy-MM-dd HH:mm:ss") + "' AND";
                }
                else
                {
                    sqlwhere += " CHECKINOUT.CHECKTIME>='" + Convert.ToDateTime(checkInOutInfo.starttime).ToString("yyyy-MM-dd") + "' AND";
                }
            }
            if (!string.IsNullOrEmpty(checkInOutInfo.endtime))
            {
                if (Convert.ToDateTime(checkInOutInfo.endtime).ToString("HH:mm:ss")!="00:00:00")
                {
                    sqlwhere += " CHECKINOUT.CHECKTIME<'" + Convert.ToDateTime(checkInOutInfo.endtime).ToString("yyyy-MM-dd HH:mm:ss") + "' AND";
                }
                else
                {
                    sqlwhere += " CHECKINOUT.CHECKTIME<'" + Convert.ToDateTime(checkInOutInfo.endtime).AddDays(1).ToString("yyyy-MM-dd") + "' AND";
                }
            }
            if (sqlwhere.Length > 0)
            {
                strSql += " where" + sqlwhere.Substring(0, sqlwhere.Length - 4) + " order by CHECKINOUT.USERID,CHECKINOUT.CHECKTIME asc";
            }

            return SqlHelper<AttendanceInfo>.SqlServerList(strSql);
        }

        public bool AddOwner(string OwnerName, string UUID, string EmployeeNo, string Department, int CheckType, int DateType)
        {
            string strSql = string.Format(@"INSERT into owner_info(OwnerName,OwnerType,UUID,VirtualCard,projectID,EmployeeNo,Department,CheckType,DateType)VALUES('{0}',{1},'{2}',{3},{4},'{5}','{6}',{7},{8})",
                                           OwnerName, 1, UUID, 0, 1, EmployeeNo, Department, CheckType, DateType);
            return SqlHelper<OwnerInfo>.SqlExecute(strSql);
        }

        public List<string> QueryDepart()
        {
            string strSql = "SELECT DISTINCT(Department) FROM owner_info";
            return SqlHelper<string>.SqlList(strSql);
        }

        public List<OwnerInfo> QueryOwnerByDepart(string depart)
        {
            string strSql = "SELECT * FROM owner_info WHERE Department='" + depart + "' AND IsDelete=0 AND CheckType!=4";
            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }

        public List<AttendanceInfo> QueryAttendanceRecord(DateTime date, string EmployeeNo)
        {
            string strSql = "SELECT USERINFO.USERID,CHECKINOUT.CHECKTIME,USERINFO.NAME,USERINFO.BADGENUMBER,CHECKINOUT.Memoinfo,CHECKINOUT.RecordID FROM CHECKINOUT INNER JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID WHERE CHECKINOUT.CHECKTIME>='" + date.ToString("yyyy-MM-dd") + "' AND CHECKINOUT.CHECKTIME<'" + date.AddDays(1).ToString("yyyy-MM-dd") + "' AND USERINFO.BADGENUMBER='" + EmployeeNo + "' ORDER BY CHECKINOUT.CHECKTIME";
            return SqlHelper<AttendanceInfo>.SqlServerList(strSql);
        }

        public List<AttendanceInfo> QueryAllAttendanceRecord(DateTime start, DateTime end)
        {
            string strSql = "SELECT USERINFO.USERID,CHECKINOUT.CHECKTIME,USERINFO.NAME,USERINFO.BADGENUMBER,CHECKINOUT.Memoinfo,CHECKINOUT.RecordID FROM CHECKINOUT INNER JOIN USERINFO ON USERINFO.USERID = CHECKINOUT.USERID WHERE CHECKINOUT.CHECKTIME>='" + start.ToString("yyyy-MM-dd") + "' AND CHECKINOUT.CHECKTIME<'" + end.AddDays(1).ToString("yyyy-MM-dd") + "' ORDER BY CHECKINOUT.CHECKTIME";
            return SqlHelper<AttendanceInfo>.SqlServerList(strSql);
        }

        public List<Department> QueryKQDepart()
        {
            string strSql = "select DEPTNAME,DEPTID from DEPARTMENTS WHERE SUPDEPTID=1";

            return SqlHelper<Department>.SqlServerList(strSql);
        }

        public List<BuildInfo> QueryAllDepart(int projectid)
        {
            string strSql = "SELECT * FROM building_info where IsDelete=0 AND projectID=" + projectid;
            string sqlwhere = string.Empty;

            return SqlHelper<BuildInfo>.SqlList(strSql);
        }

        public List<OwnerInfo> QueryOwnerByBuildID(int buildid)
        {
            string strSql = "SELECT * FROM vi_owner WHERE BuildID=" + buildid + " AND IsDelete=0 AND CheckType!=4 AND OwnerName NOT LIKE '%离职%'";
            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }

        public int QueryEmployeeNo(string EmployeeNo)
        {
            string strSql = "SELECT COUNT(1) FROM USERINFO WHERE BADGENUMBER='" + EmployeeNo + "'";

            return SqlHelper<int>.SqlServerScalar(strSql);
        }

        public int QueryDepartID(string name)
        {
            string strSql = "SELECT DEPTID FROM DEPARTMENTS WHERE DEPTNAME='" + name + "'";

            return SqlHelper<int>.SqlServerScalar(strSql);
        }

        public bool InsertKQUser(string name, string EmployeeNo, int departid)
        {
            string strSql = string.Format("INSERT INTO USERINFO(BADGENUMBER,NAME,DEFAULTDEPTID)VALUES('{0}','{1}',{2})", EmployeeNo, name, departid);

            return SqlHelper<int>.SqlServerExecute(strSql);
        }

        public bool UpdateKQUser(string name, string EmployeeNo, int departid, int userid)
        {
            string strSql = string.Format("UPDATE USERINFO SET NAME='{1}',DEFAULTDEPTID={2},BADGENUMBER='{0}' WHERE USERID={3}", EmployeeNo, name, departid, userid);

            return SqlHelper<int>.SqlServerExecute(strSql);
        }

        public bool DeleteKQUser(string EmployeeNo)
        {
            string strSql = string.Format("DELETE FROM USERINFO WHERE BADGENUMBER='{0}'", EmployeeNo);

            return SqlHelper<int>.SqlServerExecute(strSql);
        }

        public int QueryUserID(string name, string EmployeeNo)
        {
            string strSql = "SELECT USERID FROM USERINFO WHERE BADGENUMBER='" + EmployeeNo + "' AND NAME='" + name + "'";

            return SqlHelper<int>.SqlServerScalar(strSql);
        }

        public List<MissingClockIn> QueryAllMissingClockIn(DateTime start_date, DateTime end_date)
        {
            string sql = "SELECT STR_TO_DATE(data_95, '%Y-%m-%d') AS date,run_id,data_95,data_98,data_99,data_100,data_101,data_102,data_103,data_104,data_105,data_121,data_116,data_118 FROM flow_data_284 WHERE STR_TO_DATE(data_95, '%Y-%m-%d')>='" + start_date.ToString("yyyy-MM-dd") + "' AND STR_TO_DATE(data_95, '%Y-%m-%d')<='" + end_date.ToString("yyyy-MM-dd") + "' AND data_116!=''";
            return SqlHelper<MissingClockIn>.OASqlList(sql);
        }

        public List<PublicRelease> QueryAllPublicRelease(DateTime start_date, DateTime end_date)
        {
            //string sql =string.Format("SELECT  data_6,data_7,run_id,data_13 FROM flow_data_351 WHERE data_17!='' AND (STR_TO_DATE(data_6, '%Y-%m-%d')>='{0}' AND STR_TO_DATE(data_7, '%Y-%m-%d')<='{0}') OR (STR_TO_DATE(data_6, '%Y-%m-%d')>='{1}' AND STR_TO_DATE(data_7, '%Y-%m-%d')<='{1}')", start_date.ToString("yyyy-MM-dd"), end_date.ToString("yyyy-MM-dd"));
            string sql = string.Format("SELECT  data_6,data_7,run_id,data_13 FROM flow_data_351 WHERE data_17!='' AND (STR_TO_DATE(data_6, '%Y-%m-%d')>='{0}') ", start_date.ToString("yyyy-MM-dd"), end_date.ToString("yyyy-MM-dd"));
            return SqlHelper<PublicRelease>.OASqlList(sql);
        }

        public List<LeaveInfo> QueryAllLeave(DateTime start_date, DateTime end_date)
        {
            string sql = string.Format("SELECT  data_80,data_81,run_id,data_86,data_94,data_95,data_82,data_89 FROM flow_data_352 WHERE data_94!='' AND ((STR_TO_DATE(data_80, '%Y-%m-%d')>='{0}' AND STR_TO_DATE(data_81, '%Y-%m-%d')>='{0}')  or(STR_TO_DATE(data_80, '%Y-%m-%d')>='{1}' AND STR_TO_DATE(data_81, '%Y-%m-%d')>='{1}'))", start_date.ToString("yyyy-MM-dd"), end_date.ToString("yyyy-MM-dd"));
            return SqlHelper<LeaveInfo>.OASqlList(sql);
        }

        public List<WorkOvertime> QueryWorkOverTime(DateTime start_date, DateTime end_date)
        {
            string sql = "SELECT  data_13,data_14,run_id,data_18,data_19,data_20,data_47,data_22,data_17,SUBSTRING(data_24, 5) AS jbsj FROM flow_data_350 WHERE data_24!='' AND data_23='是' AND STR_TO_DATE(SUBSTRING(data_24, 5), '%Y-%m-%d')>='" + start_date.ToString("yyyy-MM-dd") + "' AND STR_TO_DATE(SUBSTRING(data_24, 5), '%Y-%m-%d')<='" + end_date.ToString("yyyy-MM-dd") + "'";
            return SqlHelper<WorkOvertime>.OASqlList(sql);
        }

        public List<OwnerInfo> QueryOwner()
        {
            string strSql = "SELECT * FROM vi_owner WHERE IsDelete=0 AND CheckType!=4 ORDER BY Department";
            return SqlHelper<OwnerInfo>.SqlList(strSql);
        }
    }
}
